package com.cg.dao;

import com.cg.pojo.User;
import com.cg.utl.JDBCUtil;

import java.sql.Connection;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.sql.Timestamp;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class UserDao {
    /**
     * 获取用户列表
     * @return 用户的参数
     */
    public List<User> selectAll(){
        try{
            List<User> users = new ArrayList<>();
            JDBCUtil util=JDBCUtil.getInstance();
            String sql="select * from tb_user order by createTime DESC";
            ResultSet rs=util.query(sql);
            while(rs.next()){
                //获取参数
                User user = new User();
                user.setId(rs.getInt("id"));
                user.setUsername(rs.getString("username"));
                user.setRole(rs.getString("role"));
                user.setSex(rs.getInt("sex"));
                user.setCreateTime(rs.getTimestamp("createTime"));
                users.add(user);
            }
            util.close();
            return users;
        }catch (SQLException e){
            e.printStackTrace();
            return null;
        }
    }
    /**
     * 获取用户总数量,来判断是否添加成功
     * @param username 用户编号
     * @return 总数量
     */
    public int getTotalByUserName(String username){
        try{
            JDBCUtil util=JDBCUtil.getInstance();
            //判断用户编号是否重复
            String selSQL="select count(1) as total from tb_user where username=?";
            List<Object> selParams=new ArrayList<>();
            selParams.add(username);
            ResultSet rs=util.query(selSQL,selParams);
            rs.next();
            int total=rs.getInt("total");
            util.close();
            return total;
        }catch (SQLException ex){
            ex.printStackTrace();
            return -1;
        }
    }

    /**
     * 新增用户列表
     */
    public boolean addUser(User user){
        String insertSql="INSERT INTO tb_user(username,password,role,sex,age,createTime)" +
                "Values(?,?,?,?,?,?)";
        //获取参数
        JDBCUtil util = JDBCUtil.getInstance();
        List<Object> ps=new ArrayList<>();
        ps.add(user.getUsername());
        ps.add(user.getPassword());
        ps.add(user.getRole());
        ps.add(user.getSex());
        ps.add(user.getAge());
        ps.add(new Timestamp(System.currentTimeMillis()));//自动获取时间的值

        boolean result=util.update(insertSql,ps);
        util.close();
        return result;
    }

    /**
     * 实现删除功能
     */
    public boolean delete(String id){
        JDBCUtil util=JDBCUtil.getInstance();
        String deleteSQL="delete from tb_user where id=?";
        List<Object> ps=new ArrayList<>();
        ps.add(id);
        boolean result=util.update(deleteSQL,ps);
        util.close();
        return result;
    }

}
